﻿using Dapper;
using Oracle.ManagedDataAccess.Client;
using System.Data;

namespace ClassLibrary_fqy_NET
{
    internal class OracleHelper
    {
        //服务器数据库连接字符串
        private const string connectionString = "User Id=STK_FQY;Password=123456;Data Source=//192.168.0.237:1521/pasorcl;";

        //服务器数据库管理员连接字符串
        //private readonly string connectionString = "User Id=system;Password=pasorcl###2025;Data Source=//192.168.0.237:1521/pasorcl;";

        //MES数据库连接字符串
        // private readonly string connectionString = "User Id=user_gzkf01;Password=gzkf01@861759;Data Source=//192.168.0.227:1521/orclmes;";

        private OracleConnection oracleConn;

        /// <summary>
        /// 使用内部固定连接字符串实例化OracleConnection
        /// </summary>
        public OracleHelper()
        {
            oracleConn = new OracleConnection(connectionString);
        }
        /// <summary>
        /// 使用指定连接字符串实例化OracleConnection
        /// </summary>
        /// <param name="connStr">指定连接字符串</param>
        public OracleHelper(string connStr)
        {
            oracleConn = new OracleConnection(connStr);
        }
        /// <summary>
        /// 使用指定连接字符串实例化OracleConnection
        /// </summary>
        /// <param name="id">数据库用户名</param>
        /// <param name="password">数据库密码</param>
        /// <param name="datasoure">连接服务器名称</param>
        public OracleHelper(string id, string password, string datasoure)
        {
            var strConn = $"User Id={id};Password={password};Data Source={datasoure};";
            oracleConn = new OracleConnection(strConn);
        }
        /// <summary>
        /// 连接数据库(异步)
        /// </summary>
        /// <param name="token">带取消标记</param>
        /// <returns></returns>
        public async Task<int> ConnectAsync(CancellationToken token)
        {
            if (oracleConn.State == System.Data.ConnectionState.Open)
            {
                return 0;
            }

            try
            {
                await oracleConn.OpenAsync(token);
            }
            catch (OracleException)
            {
                return -1;//
            }
            catch (OperationCanceledException)
            {
                return -2; // 取消连接
            }
            return 0;
        }
        /// <summary>
        /// 连接数据库(异步)
        /// </summary>
        /// <returns></returns>
        public async Task<int> ConnectAsync()
        {
            if (oracleConn.State == System.Data.ConnectionState.Open)
            {
                return 0;
            }

            try
            {
                await oracleConn.OpenAsync();
            }
            catch (OracleException)
            {
                return -1;//
            }

            return 0;
        }
        /// <summary>
        /// 查询数据(同步)、自动连接数据库
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="msg">返回查询信息</param>
        /// <returns></returns>
        public DataTable QureyData(string sql, out string msg)
        {
            DataTable dt = new DataTable();
            msg = "complete";
            try
            {
                using (OracleCommand cmd = oracleConn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    using (OracleDataAdapter oda = new OracleDataAdapter(cmd))
                    {
                        oda.Fill(dt);//fill方法内部会自动打开和关闭连接
                    }
                }
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }

            return dt;
        }
        /// <summary>
        /// 查询数据(异步)、自动连接数据库
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public async Task<DataTable> QureyDataAsync(string sql)
        {
            return await Task.Run(() =>
            {
                return QureyData(sql, out string msg);
            });
        }
        /// <summary>
        /// 查询数据(异步)、自动连接数据库
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="reserve">备用参数</param>
        /// <returns></returns>
        public async Task<(DataTable, string)> QureyDataAsync(string sql, byte reserve)
        {
            return await Task.Run(() =>
            {
                var dt = QureyData(sql, out string msg);
                return (dt, msg);
            });
        }


        /// <summary>
        /// 查询数据(异步)、自动连接数据库
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="connstr"></param>
        /// <returns></returns>
        public static async Task<List<T>> QureyDataAsync<T>(string sql, string connstr = connectionString) where T : class
        {
            try
            {
                using (var conn = new OracleConnection(connstr))
                {
                    var result = await conn.QueryAsync<T>(sql);
                    return result.ToList();
                }
            }
            catch
            {
                return new List<T>();
            }
        }
        /// <summary>
        /// 插入数据(异步)、自动连接数据库
        /// 适用于单条记录插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="connstr"></param>
        /// <returns></returns>
        public static async Task<int> InsertDataAsync<T>(string sql, object param, string connstr = connectionString) where T : class
        {
            try
            {
                using (var conn = new OracleConnection(connstr))
                {
                    var result = await conn.ExecuteAsync(sql, param);
                    return result;
                }
            }
            catch
            {
                return 0;
            }
        }
        /// <summary>
        /// 插入数据(事务)(异步)、自动连接数据库
        /// 批量插入、业务逻辑复杂的插入、生产环境推荐使用事务
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="connstr"></param>
        /// <returns></returns>
        public static async Task<int> InsertData_TransactionAsync<T>(string sql, object param, string connstr = connectionString) where T : class
        {
            try
            {
                using (var conn = new OracleConnection(connstr))
                {
                    await conn.OpenAsync();
                    using (var transaction = conn.BeginTransaction())
                    {
                        try
                        {
                            var result = await conn.ExecuteAsync(sql, param, transaction);
                            if (result > 0)
                            {
                                await transaction.CommitAsync();
                            }
                            else
                            {
                                await transaction.RollbackAsync();
                            }
                            return result;
                        }
                        catch
                        {
                            await transaction.RollbackAsync();
                            return 0;
                        }

                    }
                }
            }
            catch
            {
                return 0;
            }
        }
        /// <summary>
        /// 插入数据(事务)(异步)、自动连接数据库
        /// 批量插入、业务逻辑复杂的插入、生产环境推荐使用事务
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="connstr"></param>
        /// <returns></returns>
        public static async Task<(int,string)> InsertData_TransactionAsync<T>(string sql, T param, string connstr = connectionString) where T : class
        {
            try
            {
                using (var conn = new OracleConnection(connstr))
                {
                    await conn.OpenAsync();
                    using (var transaction = conn.BeginTransaction())
                    {
                        try
                        {
                            var result = await conn.ExecuteAsync(sql, param, transaction);
                            if (result > 0)
                            {
                                await transaction.CommitAsync();
                            }
                            else
                            {
                                await transaction.RollbackAsync();
                            }
                            return (result,"complete");
                        }
                        catch(Exception ex)
                        {
                            await transaction.RollbackAsync();
                            return (0, ex.Message);
                        }

                    }
                }
            }
            catch(Exception ex)
            {
                return (0, ex.Message);
            }
        }














    }
}
